#!/usr/bin/env python
"""
重新分配课程
@file admin/reassign_courses.py
@description 清除错误的课程分配后，重新为教师分配正确的课程
@created 2024-07-25
"""

import os
import django
import random
from datetime import datetime, timedelta

# 设置Django环境
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'ai_teach.settings')
django.setup()

from django.db import connection
import logging

# 设置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

def reassign_courses():
    """
    为教师重新分配正确的课程
    确保每个学院的课程只分配给该学院的班级和教师
    """
    try:
        with connection.cursor() as cursor:
            # 学院和它们对应的课程
            college_courses = {
                1: [  # 计算机学院
                    "数据结构", "计算机组成原理", "操作系统", "计算机网络", 
                    "数据库原理", "软件工程", "人工智能", "算法设计与分析"
                ],
                2: [  # 法学院
                    "法学导论", "宪法学", "民法学", "刑法学"
                ],
                3: [  # 经济学院
                    "经济学原理", "金融学", "国际经济学", "财务管理", "会计学原理", "统计学"
                ],
                4: [  # 工程学院
                    "高等数学", "线性代数", "概率论与数理统计", "物理学", "工程力学"
                ],
                5: [  # 管理学院
                    "管理学原理", "市场营销", "人力资源管理", "组织行为学"
                ]
            }
            
            # 学院和它们对应的班级匹配模式
            college_class_patterns = {
                1: ["计算机", "软件", "人工智能", "网络"],  # 计算机学院
                2: ["法学", "国际法"],  # 法学院
                3: ["经济", "金融", "贸易"],  # 经济学院
                4: ["工程", "建筑"],  # 工程学院
                5: ["管理", "会计"]  # 管理学院
            }
            
            # 检查各个学院的课程数量
            for college_id, course_names in college_courses.items():
                # 获取学院名称
                cursor.execute("SELECT name FROM colleges WHERE id = %s", [college_id])
                college_name = cursor.fetchone()[0]
                
                # 检查每个课程是否存在
                for course_name in course_names:
                    cursor.execute("SELECT COUNT(*) FROM courses WHERE name LIKE %s", [f'%{course_name}%'])
                    count = cursor.fetchone()[0]
                    
                    if count == 0:
                        logger.warning(f"学院 {college_name} 的课程 '{course_name}' 不存在，将创建该课程")
                        
                        # 获取该学院的一名教师
                        cursor.execute("SELECT teacher_id FROM teacher WHERE college_id = %s LIMIT 1", [college_id])
                        teacher_result = cursor.fetchone()
                        
                        if teacher_result:
                            teacher_id = teacher_result[0]
                            
                            # 创建课程
                            cursor.execute("""
                                INSERT INTO courses (name, description, teacher_id, college_id, credits, created_at)
                                VALUES (%s, %s, %s, %s, %s, %s)
                            """, [
                                course_name,
                                f"{course_name}课程描述",
                                teacher_id,
                                college_id,
                                random.randint(2, 4),
                                datetime.now()
                            ])
                            
                            logger.info(f"创建课程: {course_name}, 学院: {college_name}")
                        else:
                            logger.warning(f"学院 {college_name} 没有教师，无法创建课程 {course_name}")
            
            # 为每个学院的教师分配课程
            for college_id in range(1, 6):
                # 获取学院名称
                cursor.execute("SELECT name FROM colleges WHERE id = %s", [college_id])
                college_name = cursor.fetchone()[0]
                
                # 获取该学院的所有教师
                cursor.execute("""
                    SELECT teacher_id, teacher_name 
                    FROM teacher 
                    WHERE college_id = %s AND status = 1
                    LIMIT 10
                """, [college_id])
                
                teachers = cursor.fetchall()
                if not teachers:
                    logger.warning(f"学院 {college_name} 没有教师，跳过课程分配")
                    continue
                
                logger.info(f"为学院 {college_name} 的 {len(teachers)} 名教师分配课程")
                
                # 获取该学院的所有课程
                course_keywords = college_courses.get(college_id, [])
                conditions = " OR ".join([f"name LIKE '%{keyword}%'" for keyword in course_keywords])
                
                cursor.execute(f"SELECT course_id, name FROM courses WHERE {conditions}")
                courses = cursor.fetchall()
                
                if not courses:
                    logger.warning(f"学院 {college_name} 没有对应的课程，跳过课程分配")
                    continue
                
                logger.info(f"学院 {college_name} 有 {len(courses)} 门课程")
                
                # 获取该学院的班级
                class_patterns = college_class_patterns.get(college_id, [])
                if not class_patterns:
                    logger.warning(f"学院 {college_name} 没有定义班级匹配模式，跳过课程分配")
                    continue
                
                pattern_conditions = " OR ".join([f"name LIKE '%{p}%'" for p in class_patterns])
                cursor.execute(f"SELECT id, name FROM school_classes WHERE {pattern_conditions}")
                
                classes = cursor.fetchall()
                if not classes:
                    logger.warning(f"未找到学院 {college_name} 的班级，跳过课程分配")
                    continue
                
                logger.info(f"学院 {college_name} 有 {len(classes)} 个班级")
                
                # 为每个教师分配1-3门课程，每门课程分配给1-2个班级
                for teacher_id, teacher_name in teachers:
                    # 检查教师是否已有教学记录
                    cursor.execute("SELECT COUNT(*) FROM teaching_record WHERE teacher_id = %s", [teacher_id])
                    if cursor.fetchone()[0] > 0:
                        logger.info(f"教师 {teacher_name} 已有教学记录，跳过")
                        continue
                    
                    # 随机选择1-3门课程
                    num_courses = min(random.randint(1, 3), len(courses))
                    selected_courses = random.sample(courses, num_courses)
                    
                    for course_id, course_name in selected_courses:
                        # 随机选择1-2个班级
                        num_classes = min(random.randint(1, 2), len(classes))
                        selected_classes = random.sample(classes, num_classes)
                        
                        for class_id, class_name in selected_classes:
                            # 检查这个组合是否已经存在
                            cursor.execute("""
                                SELECT COUNT(*) FROM teaching_record
                                WHERE teacher_id = %s AND course_id = %s AND class_id = %s
                            """, [teacher_id, course_id, class_id])
                            
                            if cursor.fetchone()[0] == 0:
                                # 创建教学记录
                                cursor.execute("""
                                    INSERT INTO teaching_record (teacher_id, course_id, class_id, semester_id, start_date, end_date, status)
                                    VALUES (%s, %s, %s, %s, %s, %s, 1)
                                """, [
                                    teacher_id,
                                    course_id,
                                    class_id,
                                    1,  # 学期ID
                                    datetime.now() - timedelta(days=90),  # 开课日期（3个月前）
                                    datetime.now() + timedelta(days=90),  # 结课日期（3个月后）
                                ])
                                
                                # 创建课程安排
                                day_of_week = random.randint(0, 4)  # 周一至周五
                                start_time = random.randint(1, 7)  # 第1-7节
                                end_time = start_time + 1
                                
                                cursor.execute("""
                                    INSERT INTO course_schedules (course_id, teacher_id, day_of_week, start_time, end_time, classroom)
                                    VALUES (%s, %s, %s, %s, %s, %s)
                                """, [
                                    course_id,
                                    teacher_id,
                                    day_of_week,
                                    start_time,
                                    end_time,
                                    f"教学楼{random.randint(1, 5)}-{random.randint(101, 501)}"
                                ])
                                
                                logger.info(f"分配课程: 教师 {teacher_name}, 课程 {course_name}, 班级 {class_name}")
                                
                                # 获取班级学生列表
                                cursor.execute("SELECT student_id FROM student_class WHERE class_id = %s", [class_id])
                                student_ids = [row[0] for row in cursor.fetchall()]
                                
                                # 为每个学生生成成绩和出勤记录
                                for student_id in student_ids:
                                    # 成绩
                                    final_grade = round(random.uniform(60, 100), 1)
                                    cursor.execute("""
                                        INSERT INTO grades (student_id, course_id, final_grade)
                                        VALUES (%s, %s, %s)
                                    """, [student_id, course_id, final_grade])
                                    
                                    # 出勤记录（假设有10次课）
                                    for i in range(10):
                                        status = 1 if random.random() > 0.1 else 0  # 90%出勤率
                                        cursor.execute("""
                                            INSERT INTO attendance_records (student_id, course_id, class_date, status)
                                            VALUES (%s, %s, %s, %s)
                                        """, [
                                            student_id,
                                            course_id,
                                            datetime.now() - timedelta(days=90-i*7),  # 每周一次课
                                            status
                                        ])
            
            logger.info("重新分配课程完成")
            
    except Exception as e:
        logger.error(f"重新分配课程失败: {str(e)}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    reassign_courses() 